﻿using System;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.EffiProz;
using System.Transactions;

namespace EffiProz.Test
{
    /// <summary>
    /// Summary description for TestAllTypes
    /// </summary>
    [TestClass]
    public class TestAllTypes : BaseTest
    {
        public TestAllTypes()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void AllTypesTest1()
        {
            string connString = "Connection Type=Memory ; Initial Catalog=alltypes; User=sa; Password=;";
            DbConnection conn = Setup(connString);
            RunTests(conn);
            conn.Close();
        }

        [TestMethod]
        public void AllTypesTest2()
        {
            string connString = "Connection Type=File ; Initial Catalog=../../../EffiProzTest/Data/AdoAllTypes/Test; User=sa; Password=;";
            DbConnection conn = Setup(connString);
            RunTests(conn);

            DbCommand command = conn.CreateCommand();
            command.CommandText = "SHUTDOWN;";
            conn.Close();

            conn = new EfzConnection(connString);
            conn.Open();
            RunTests(conn);

            command.CommandText = "CHECKPOINT DEFRAG;";
            conn.Close();
        }

        private DbConnection Setup(string connString)
        {
            string sql = String.Empty;

            EfzConnection conn = new EfzConnection(connString);
           
                conn.Open();

                DbCommand command = conn.CreateCommand();
                sql = "DROP TABLE alltypes IF EXISTS;";
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = "CREATE CACHED TABLE alltypes (\n"
            + "    id INTEGER,\n"
            + "    ti TINYINT,\n"
            + "    si SMALLINT,\n"
            + "    i INTEGER,\n"
            + "    bi BIGINT,\n"
            + "    n NUMERIC(5,2),\n"
            + "    f FLOAT(5),\n"
            + "    r DOUBLE,\n"
            + "    b BOOLEAN,\n"
            + "    c CHARACTER(3),\n"
            + "    cv CHARACTER VARYING(3),\n"
            + "    bt BIT(9),\n"
            + "    btv BIT VARYING(3),\n"
            + "    d DATE,\n"
            + "    t TIME(2),\n"
            + "    tw TIME(2) WITH TIME ZONE,\n"
            + "    ts TIMESTAMP(2),\n"
            + "    tsw TIMESTAMP(2) WITH TIME ZONE,\n"
            + "    bin BINARY(4),\n"
            + "    vb VARBINARY(4),\n"
            + "    dsival INTERVAL DAY(5) TO SECOND(6),\n"
            + "    sival INTERVAL SECOND(6,4)\n"
           + ")";

                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = "INSERT INTO alltypes VALUES (\n"
             + "    1, 3, 4, 5, 6, 7.8, 8.9, 9.7, true, 'ab', 'cd',\n"
             + "    b'10', b'10', current_date, '13:14:00',\n"
             + "    '15:16:00', '2009-02-09 16:17:18', '2009-02-09 17:18:19',\n"
             + "    x'A103', x'A103', "
             + "INTERVAL '145 23:12:19.345' DAY TO SECOND,\n"
             + "    INTERVAL '1000.345' SECOND\n"
             + ')';
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = "INSERT INTO alltypes VALUES (\n"
             + "    3, 3, 4, 5, 6, 7.8, 8.9, 9.7, true, 'ab', 'cd',\n"
             + "    b'10', b'10', current_date, '13:14:00',\n"
             + "    '15:16:00', '2009-02-09 16:17:18', '2009-02-09 17:18:19',\n"
             + "    x'A103', x'A103', "
             + "    INTERVAL '145 23:12:19.345' DAY TO SECOND,\n"
             + "    INTERVAL '1000.345' SECOND\n"
             + ')';
                command.CommandText = sql;
                command.ExecuteNonQuery();

                command.CommandText = "CHECKPOINT;";
                command.ExecuteNonQuery();

                return conn;
        }



       
        void RunTests(DbConnection conn)
        {

            TestIntegerSimpleRead(conn);
            TestTinyIntSimpleRead(conn);
            TestSmallIntSimpleRead(conn);
            TestBigIntSimpleRead(conn);
            TestNumericSimpleRead(conn);
            TestFloatSimpleRead(conn);
            TestDoubleSimpleRead(conn);
            TestBooleanSimpleRead(conn);
            TestCharSimpleRead(conn);
            TestVarCharSimpleRead(conn);
            TestFixedStringSimpleRead(conn);
            TestDerivedStringSimpleRead(conn);
            TestDateSimpleRead(conn);
            TestTimeSimpleRead(conn);
            TestTimeWSimpleRead(conn);
            TestTimeSimpleRead(conn);
            TestTimestampWSimpleRead(conn);
            TestBitSimpleRead(conn);
            TestBitVaryingSimpleRead(conn);
            TestBinarySimpleRead(conn);
            TestVarBinarySimpleRead(conn);
            TestDaySecIntervalSimpleRead(conn);
            TestSecIntervalSimpleRead(conn);

            TestIntegerComplex(conn);
            TestTinyIntComplex(conn);
            TestSmallIntComplex(conn);
            TestBigIntComplex(conn);
            TestFloatComplex(conn);
            TestDoubleComplex(conn);
            TestBooleanComplex(conn);
            TestCharComplex(conn);
            TestVarCharComplex(conn);
            TestDateComplex(conn);
            TestTimeComplex(conn);
            TestTimestampComplex(conn);
            TestTimestampWComplex(conn);
            TestBitComplex(conn);
            TestBitVaryingComplex(conn);
            TestBinaryComplex(conn);
            TestVarBinaryComplex(conn);
        }

        void TestIntegerSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(int), reader["i"].GetType());
                Assert.AreEqual(5, reader.GetInt32(reader.GetOrdinal("i")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestTinyIntSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte), reader["ti"].GetType());
                Assert.AreEqual(3, reader.GetByte(reader.GetOrdinal("ti")));
                //Assert.AreEqual(3, reader.GetInt32(reader.GetOrdinal("ti")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestSmallIntSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(short), reader["si"].GetType());
                Assert.AreEqual(4, reader.GetInt16(reader.GetOrdinal("si")));
                //Assert.AreEqual(4, reader.GetInt32(reader.GetOrdinal("si")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestBigIntSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(long), reader["bi"].GetType());
                Assert.AreEqual(6, reader.GetInt64(reader.GetOrdinal("bi")));               
                Assert.IsFalse(reader.Read());
            }
        }

        void TestNumericSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(decimal), reader["n"].GetType());
                Assert.AreEqual(7.8m, reader.GetDecimal(reader.GetOrdinal("n")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestFloatSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(double), reader["f"].GetType());
                Assert.AreEqual(8.9f, reader.GetFloat(reader.GetOrdinal("f")));
                Assert.AreEqual(8.9, reader.GetDouble(reader.GetOrdinal("f")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestDoubleSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(double), reader["r"].GetType());
                Assert.AreEqual(9.7, reader.GetDouble(reader.GetOrdinal("r")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestBooleanSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(bool), reader["b"].GetType());
                Assert.AreEqual(true, reader.GetBoolean(reader.GetOrdinal("b")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestCharSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(string), reader["c"].GetType());
                Assert.AreEqual("ab ", reader.GetString(reader.GetOrdinal("c")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestVarCharSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(string), reader["cv"].GetType());
                Assert.AreEqual("cd", reader.GetString(reader.GetOrdinal("cv")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestFixedStringSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT i, 'fixed str' fs, cv\n"
                    + "FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(string), reader["fs"].GetType());
                Assert.AreEqual("fixed str", reader.GetString(reader.GetOrdinal("fs")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestDerivedStringSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT i, cv || 'appendage' app, 4\n"
                    + "FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(string), reader["app"].GetType());
                Assert.AreEqual("cdappendage", reader.GetString(reader.GetOrdinal("app")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestDateSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(DateTime), reader["d"].GetType());
                Assert.AreEqual(DateTime.Now.Date, reader.GetDateTime(reader.GetOrdinal("d")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestTimeSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(DateTime), reader["t"].GetType());
                Assert.AreEqual(DateTime.MinValue.Add(TimeSpan.Parse("13:14:00")), 
                    reader.GetDateTime(reader.GetOrdinal("t")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestTimeWSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(DateTime), reader["tw"].GetType());
                Assert.AreEqual(DateTime.MinValue.Add(TimeSpan.Parse("15:16:00")), 
                    reader.GetDateTime(reader.GetOrdinal("tw")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestTimestampSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(DateTime), reader["ts"].GetType());
                Assert.AreEqual(DateTime.Parse("2009-02-09 16:17:18"),
                    reader.GetDateTime(reader.GetOrdinal("ts")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestTimestampWSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(DateTime), reader["tsw"].GetType());
                Assert.AreEqual(DateTime.Parse("2009-02-09 17:18:19"),
                    reader.GetDateTime(reader.GetOrdinal("tsw")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestBitSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte[]), reader["bt"].GetType());
                Assert.AreEqual("000000010", reader.GetString(reader.GetOrdinal("bt")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestBitVaryingSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte[]), reader["btv"].GetType());
                Assert.AreEqual("10", reader.GetString(reader.GetOrdinal("btv")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestBinarySimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                byte[] expectedBytes = new byte[] {
                 (byte) 0x03,(byte) 0xa1, (byte) 0, (byte) 0  
                };
                byte[] ba ;

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte[]), reader["bin"].GetType());
                Assert.AreEqual("0000A103", reader.GetString(reader.GetOrdinal("bin")),true);
               
                ba = (byte[])reader["bin"];

                for (int i = 0; i < ba.Length; i++)
                {
                    Assert.AreEqual(expectedBytes[i], ba[i]);
                }
            }
        }

        void TestVarBinarySimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                byte[] expectedBytes = new byte[] {
                 (byte) 0x03,(byte) 0xa1
                };
                byte[] ba;

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte[]), reader["vb"].GetType());
                Assert.AreEqual("A103", reader.GetString(reader.GetOrdinal("vb")), true);

                ba = (byte[])reader["vb"];

                for (int i = 0; i < ba.Length; i++)
                {
                    Assert.AreEqual(expectedBytes[i], ba[i]);
                }
            }
        }

        void TestDaySecIntervalSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());              
                Assert.AreEqual("145 23:12:19.345000", reader.GetString(reader.GetOrdinal("dsival")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestSecIntervalSimpleRead(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "SELECT * FROM alltypes WHERE id in (1, 2)";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());        
                Assert.AreEqual("1000.3450", reader.GetString(reader.GetOrdinal("sival")));
                Assert.IsFalse(reader.Read());
            }
        }


        void TestIntegerComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, i) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = 495;
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = 500;
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT * FROM alltypes WHERE i = @value";
                para_value.Value = 495;
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(int), reader["i"].GetType());
                Assert.AreEqual(495, reader.GetInt32(reader.GetOrdinal("i")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestTinyIntComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, ti) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = (byte)200;
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = (int)201;
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT * FROM alltypes WHERE ti = @value";
                para_value.Value = (byte)200;
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte), reader["ti"].GetType());
                Assert.AreEqual(200, reader.GetByte(reader.GetOrdinal("ti")));
                Assert.IsFalse(reader.Read());
            }
        }


        void TestSmallIntComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, si) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = (short)395;
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = (int)201;
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT * FROM alltypes WHERE si = @value";
                para_value.Value = (short)395;
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(short), reader["si"].GetType());
                Assert.AreEqual(395, reader.GetInt16(reader.GetOrdinal("si")));
                Assert.IsFalse(reader.Read());
            }
        }


        void TestBigIntComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, bi) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = (long)395;
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = 201;
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT * FROM alltypes WHERE bi = @value";
                para_value.Value = (long)395;
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(long), reader["bi"].GetType());
                Assert.AreEqual(395, reader.GetInt64(reader.GetOrdinal("bi")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestFloatComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, f) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = (float)395.9;
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = 201.1;
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT * FROM alltypes WHERE f = @value";
                para_value.Value = (float)395.9;
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(double), reader["f"].GetType());
                Assert.AreEqual((float)(double)(float)395.9, reader.GetFloat(reader.GetOrdinal("f")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestDoubleComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, r) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = (double)276.45;
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = 201.11;
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT * FROM alltypes WHERE r = @value";
                para_value.Value = 276.45;
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(double), reader["r"].GetType());
                Assert.AreEqual(276.45, reader.GetDouble(reader.GetOrdinal("r")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestBooleanComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, b) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = false;
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = true;
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT * FROM alltypes WHERE b = @value";
                para_value.Value = false;
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(bool), reader["b"].GetType());
                Assert.AreEqual(false, reader.GetBoolean(reader.GetOrdinal("b")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestCharComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, c) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = "xy";
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = "xz";
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT * FROM alltypes WHERE c = @value";
                para_value.Value = "xy ";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(string), reader["c"].GetType());
                Assert.AreEqual("xy ", reader.GetString(reader.GetOrdinal("c")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestVarCharComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, cv) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = "xy";
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = "xz";
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT * FROM alltypes WHERE cv = @value";
                para_value.Value = "xy";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(string), reader["cv"].GetType());
                Assert.AreEqual("xy", reader.GetString(reader.GetOrdinal("cv")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestDateComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, d) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = DateTime.Now.Date.AddDays(1);
                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = DateTime.Now.Date.AddDays(2);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT id, d FROM alltypes WHERE d = @value";
                para_value.Value = DateTime.Now.Date.AddDays(1);
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(DateTime), reader["d"].GetType());
                Assert.AreEqual(DateTime.Now.Date.AddDays(1), reader.GetDateTime(reader.GetOrdinal("d")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestTimeComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, t) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = DateTime.Parse("21:19:27");

                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = "22:19:27";
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT id, t FROM alltypes WHERE t = @value";
                para_value.Value = DateTime.Parse("21:19:27");
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(DateTime), reader["t"].GetType());
                Assert.AreEqual(new DateTime(DateTime.MinValue.Year,DateTime.MinValue.Month,
                    DateTime.MinValue.Day,21,19,27) ,reader.GetDateTime(reader.GetOrdinal("t")));
               // Assert.AreEqual("21:19:27", reader.GetString(reader.GetOrdinal("t")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestTimestampComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, ts) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = DateTime.Parse("2009-03-27 17:18:19");

                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = "2019-05-27 18:38:59";
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT id, ts FROM alltypes WHERE ts = @value";
                para_value.Value = DateTime.Parse("2009-03-27 17:18:19");
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(DateTime), reader["ts"].GetType());
                Assert.AreEqual(DateTime.Parse("2009-03-27 17:18:19"), reader.GetDateTime(reader.GetOrdinal("ts")));
                Assert.AreEqual("2009-03-27 17:18:19.00", reader.GetString(reader.GetOrdinal("ts")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestTimestampWComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, tsw) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = DateTime.Parse("2019-03-27 17:18:19");

                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = "2029-05-27 18:38:59";
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT id, tsw FROM alltypes WHERE tsw = @value";
                para_value.Value = DateTime.Parse("2019-03-27 17:18:19");
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(DateTime), reader["tsw"].GetType());
                Assert.AreEqual(DateTime.Parse("2019-03-27 17:18:19"), reader.GetDateTime(reader.GetOrdinal("tsw")));
                Assert.AreEqual("2019-03-27 17:18:19.00+0:00", reader.GetString(reader.GetOrdinal("tsw")));
                Assert.IsFalse(reader.Read());
            }
        }

        void TestBitComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, bt) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = "101";

                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = new byte[] { (byte)0x87, (byte)0x10, };
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT id, bt FROM alltypes WHERE bt = @value";
                para_value.Value = "101";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte[]), reader["bt"].GetType());
                Assert.AreEqual("000000101", reader.GetString(reader.GetOrdinal("bt")));
                byte[] bytes = (byte[]) reader.GetValue(reader.GetOrdinal("bt"));
                Assert.AreEqual(2, bytes.Length);
                Assert.AreEqual(0xa0 ,bytes[0]);
                Assert.AreEqual(0x00 ,bytes[1]);
                Assert.IsFalse(reader.Read());

                reader.Dispose();

                command.CommandText = "SELECT id, bt FROM alltypes WHERE bt = @value";
                para_value.Value = new byte[] { (byte)0x87, (byte)0x10, };
                reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte[]), reader["bt"].GetType());
            //    Assert.AreEqual("101111000", reader.GetString(reader.GetOrdinal("bt")));
            }
        }

        void TestBitVaryingComplex(DbConnection conn)
        {
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, btv) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = "101";

                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = new byte[] {  (byte)0x10, };
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT id, btv FROM alltypes WHERE btv = @value";
                para_value.Value = "101";
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte[]), reader["btv"].GetType());
                Assert.AreEqual("101", reader.GetString(reader.GetOrdinal("btv")));
                byte[] bytes = (byte[])reader.GetValue(reader.GetOrdinal("btv"));
                Assert.AreEqual(1, bytes.Length);
                Assert.AreEqual(0xa0, bytes[0]);                
                Assert.IsFalse(reader.Read());

                reader.Dispose();
               
            }
        }


        void TestBinaryComplex(DbConnection conn)
        {
            byte[] expectedBytes = new byte[] {
                (byte) 0xba, (byte) 0x89, (byte) 0, (byte) 0
            };        

            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, bin) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = expectedBytes;

                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = new byte[] {
                    (byte) 0xac, (byte) 0xa9, (byte) 0, (byte) 0
                 };
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT id, bin FROM alltypes WHERE bin = @value";
                para_value.Value = expectedBytes;
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte[]), reader["bin"].GetType());
                Assert.AreEqual("000089ba", reader.GetString(reader.GetOrdinal("bin")),true);
                byte[] bytes = (byte[])reader.GetValue(reader.GetOrdinal("bin"));
                Assert.AreEqual(expectedBytes.Length, bytes.Length);
                for (int i = 0; i < expectedBytes.Length; i++)
                {
                    Assert.AreEqual(expectedBytes[i], bytes[i]);
                }
                Assert.IsFalse(reader.Read());

                reader.Dispose();

            }
        }

        void TestVarBinaryComplex(DbConnection conn)
        {
            byte[] expectedBytes = new byte[] {
                (byte) 0xbb, (byte) 0xc9
            };
       
            using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = "INSERT INTO alltypes(id, vb) VALUES(@id, @value)";
                DbParameter para_id = command.CreateParameter();
                para_id.ParameterName = "@id";
                para_id.Value = 3;
                command.Parameters.Add(para_id);
                DbParameter para_value = command.CreateParameter();
                para_value.ParameterName = "@value";
                para_value.Value = expectedBytes;

                command.Parameters.Add(para_value);
                Assert.AreEqual(1, command.ExecuteNonQuery());

                para_id.Value = 4;
                para_value.Value = new byte[] {
                    (byte) 0xac, (byte) 0xf9, (byte) 0, (byte) 0
                 };
                Assert.AreEqual(1, command.ExecuteNonQuery());

                command.CommandText = "SELECT id, vb FROM alltypes WHERE vb = @value";
                para_value.Value = expectedBytes;
                DbDataReader reader = command.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(typeof(byte[]), reader["vb"].GetType());
                Assert.AreEqual("c9bb", reader.GetString(reader.GetOrdinal("vb")), true);
                byte[] bytes = (byte[])reader.GetValue(reader.GetOrdinal("vb"));
                Assert.AreEqual(expectedBytes.Length, bytes.Length);
                for (int i = 0; i < expectedBytes.Length; i++)
                {
                    Assert.AreEqual(expectedBytes[i], bytes[i]);
                }
                Assert.IsFalse(reader.Read());

                reader.Dispose();

            }
        }


    }
}
